SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  PROCEDURE [dbo].[DailyReporting_ValComm_IC2]
    @FundID VARCHAR(50) ,
    @NAVDate DATETIME = NULL ,
    @ReportName VARCHAR(100) = NULL
AS 
BEGIN

--REMEMBER TO REMOVE THE SECTION MARKED BY: %%REMOVE  (Just search for %%REMOVE)
--IT IS A PRICING OVERRIDE FOR 3.31 VALCOM WHERE PRICING 
--DID NOT EXIST IN ARTEMIS YET!!!!!
	SET FMTONLY ON;
    DECLARE @fmtonlyON BIT
    SET @fmtonlyON = 0
    IF ( 1 = 0 ) 
        BEGIN
            SET @fmtonlyON = 1
        END
    SET FMTONLY OFF;

	
    SET NOCOUNT ON;

    DECLARE @PriorNAVDATE DATETIME
    DECLARE @LastValcomExportID INT  
    DECLARE @LastValcomDate DATE  
    DECLARE @Cash DECIMAL(30, 2)
    DECLARE @netlevamt DECIMAL(30, 2)
    DECLARE @fundlevyld DECIMAL(30, 10)


	
    DECLARE @ExportID INT
    SET @ExportID = CASE WHEN @NAVDate IS NULL THEN ( SELECT
                                                        MAX(ex.ExportID)
                                                      FROM
                                                        [FSFundModel].[Reporting].Export ex
                                                      WHERE
                                                        FundID = @FundID
                                                    )
                         ELSE ( SELECT
                                    MAX(ex.ExportID)
                                FROM
                                    [FSFundModel].[Reporting].Export ex
                                WHERE
                                    NAVDate = @NAVDate
                                    AND FundID = @FundID
                              )
                    END
	
	SET @LastValcomDate = (SELECT e.NAVDate FROM [FSFundModel].[Reporting].Export e WHERE e.FundId =@FundID AND e.CreatedBy ='VALCOMM')
	SET @LastValcomExportID= (SELECT MAX(e.ExportId) FROM [FSFundModel].[Reporting].Export e WHERE e.FundId =@FundID AND e.CreatedBy ='VALCOMM')

	
    IF ( @NAVDate IS NULL ) 
        BEGIN
            SELECT
                @NAVDate = e.NAVDate
            FROM
                [FSFundModel].[Reporting].Export e
            WHERE
                e.ExportID = @ExportID
                AND FundID = @FundID
        END
    
    IF 1 = 2 
        SELECT
            NULL AS column1

    IF ( @ReportName = 'DetailDump' ) 
        BEGIN
            SELECT
                i.InstrumentId ,
                @FundID 'FundID' ,
                iss.IssuerName ,
                i.InstrName ,
                id.InstrClass ,
                i.ExtId ,
                a.AssetId ,
                a.AssQualRat ,
                a.Score ,
                i.MoodysRating ,
                a.GAAPYldPurchPrc ,
                a.GAAPYldCurrPrc ,
                a.InvWalkGAAPYld ,
                a.Cost ,
                a.BegTodayAmt ,
                a.EndAdjAmt ,
                a.AmortCostPctPar ,
                a.PurchDate ,
                a.SettlementDate ,
                a.PurchPrc ,
                a.YesterdayPrice ,
                a.TodayPrice ,
                a.DailyPrcChg ,
                a.AmortCost ,
                a.MktVal ,
                a.UnrealApprec ,
                a.UnrealDeprec ,
                a.DailyValChgAdjAmt ,
                a.DailyValChg ,
                i.Floor ,
                i.RateType ,
                i.BaseRateType ,
                i.FixedRate ,
                i.MaturityDate ,
                a.TotalIntPurchPrc ,
                a.TotalIntCurrPurchPrc ,
                a.Paydown ,
                a.Shares ,
                i.PricingVendor ,
                i.PricingMethodType ,
                a.PropReason ,
                a.PropTrack ,
                iss.MostRecentData ,
                iss.TTMEBITDA ,
                iss.IntCov ,
				i.NotionalVal,
                i.FaceLevThruTrn ,
                iss.SubSector ,
                a.EstDuration ,
                a.OID ,
                iss.IssuerDescr ,
                iss.MarketCap ,
                iss.IsPublicComp ,
                i.CovLite ,
                i.AssetPurpose
            FROM
                [FSFundModel].[Reporting].Asset a
                INNER JOIN [FSFundModel].[Reporting].Instrument i
                ON i.ExportId = a.ExportId
                   AND a.InstrumentId = i.InstrumentId
                LEFT JOIN [FSFundModel].[Reporting].FundCreditInstrument fci
                ON a.FundCreditInstrumentId = fci.FundCreditInstrumentId
                   AND a.ExportId = fci.ExportId
                INNER JOIN [FSFundModel].[Reporting].Issuer iss
                ON iss.ExportId = a.ExportId
                   AND i.IssuerId = iss.IssuerId
                INNER JOIN [FSFundModel].[Reporting].InstrumentDefinition id
                ON i.InstDefId = id.InstDefId
                   AND id.ExportId = a.ExportId
            WHERE
                a.IsActiveEndOfDay = 1
                AND a.ExportId = @ExportID
            ORDER BY
                i.InstrName ASC
        END

    SELECT
        @PriorNAVDate = PrevNAVDate ,
        @cash = CashAdj ,
        @netlevamt = netlevamt ,
        @fundlevyld = Levyld
    FROM
        [FSFundModel].[Reporting].fund f
    WHERE
        f.exportid = @exportid
        AND f.CurrNAVDate = @NAVDate
        AND f.FundID = @FundID

    DECLARE @results TABLE
(
  PortfolioID VARCHAR(50) ,
  PortfolioName VARCHAR(50) ,
  InstrClass VARCHAR(100) ,
  InstrName VARCHAR(200),
  IssuerName VARCHAR(200)
			--, AssetID VARCHAR(100)
  ,
  OrigPurchDate DATETIME ,
  IsQualified VARCHAR(2) ,
  AssetQualityRating INT ,
  PricingDepth VARCHAR(10) ,
  EstimateDuration DECIMAL(38, 12) ,
  Moody_Rating VARCHAR(50) ,
  Portfolio_Moody_Rating VARCHAR(50) ,
  Fund_Moody_Rating VARCHAR(50) ,
  GAAP_Pur VARCHAR(50) ,
  GAAP_Cur VARCHAR(50) ,
  GAAP_Walk VARCHAR(50) ,
  Sum_Par DECIMAL(38, 12) ,
  Amort_Cost_Pct_Par VARCHAR(50) ,
  Weight_Price VARCHAR(50) ,
  PriorNAV_Price DECIMAL(38, 12) ,
  NAVDate_Price DECIMAL(38, 12) ,
  Spread VARCHAR(50) ,
  Ask DECIMAL(38, 12) ,
  Bid DECIMAL(38, 12) ,
  Amort_Cost DECIMAL(38, 12) ,
  MktVal DECIMAL(38, 12) ,
  MktVal_Pct DECIMAL(38, 12) ,
  Net_Unreal_App DECIMAL(38, 12) ,
  Pct_Net_Unreal_Gain VARCHAR(50) ,
  DailyVal_Change DECIMAL(38, 12) ,
  PriceChange_Pct VARCHAR(50) ,
  SecurityID VARCHAR(50) ,
  Sum_Unreal_Dep DECIMAL(38, 12) ,
  Sum_Unreal_App DECIMAL(38, 12) ,
  Prop_Reason VARCHAR(50) ,
  Prop_Track VARCHAR(50) ,
   PricingMethod VARCHAR(50) ,
  TotalInt_Purch_Price DECIMAL(38, 12) ,
  TotalInt_Cur_Price DECIMAL(38, 12) ,
  Sum_OrigCost DECIMAL(38, 12) ,
  Security_Type VARCHAR(50) ,
  Issuer_Name VARCHAR(50) ,
  Lots DECIMAL(38, 12) ,
  Iss_SubSector VARCHAR(100) ,
  Iss_Sector VARCHAR(100) ,
  ExternalID VARCHAR(20) ,
  FocusGroup VARCHAR(5) ,
  FocusReason VARCHAR(100) ,
  portSortOrder INT ,
  instSortOrder INT ,
  priorMoodyRating VARCHAR(5) ,
  InstrumentID INT, 
  Maturity_Date DATETIME,
  Settle_Disc_prch DECIMAL(38,12),
  Pik_Prch DECIMAL(38,12),
  Coupon_Purch DECIMAL(38,12)
)

    INSERT  INTO @results
            ( PortfolioID ,
              PortfolioName ,
              InstrClass ,
              InstrName 
			  , IssuerName
			--, AssetID
              ,
              OrigPurchDate ,
              IsQualified ,
              AssetQualityRating ,
              EstimateDuration ,
              Moody_Rating ,
              Portfolio_Moody_Rating ,
              Fund_Moody_Rating ,
              GAAP_Pur ,
              GAAP_Cur ,
              GAAP_Walk ,
              Sum_Par ,
              Amort_Cost_Pct_Par ,
              Weight_Price ,
              PriorNAV_Price ,
              NAVDate_Price ,
              Amort_Cost ,
              MktVal ,
              Net_Unreal_App ,
              Pct_Net_Unreal_Gain ,
              DailyVal_Change ,
              PriceChange_Pct ,
              SecurityID ,
              Sum_Unreal_Dep ,
              Sum_Unreal_App ,
              Prop_Reason ,
			  Prop_Track,
			  PricingMethod ,
              TotalInt_Purch_Price ,
              TotalInt_Cur_Price ,
              Sum_OrigCost ,
              Security_Type ,
              Issuer_Name ,
              Lots ,
              Iss_SubSector ,
              Iss_Sector ,
              ExternalID ,
              portSortOrder ,
              instSortOrder ,
              priorMoodyRating ,
              InstrumentID,
			  Maturity_Date,
			    Settle_Disc_prch ,
				Pik_Prch ,
			  Coupon_Purch 
			  
						--, FocusReason
		    )
            SELECT
                a.PortfolioID ,
                MAX(p.PortfolioName) ,
                CASE WHEN id.InstrClass = 'Structured Notes'
                     THEN 'Collateralized Securities'
                     ELSE id.InstrClass
                END ,
                i.InstrName
				, MAX(iss.IssuerName)
		--, a.AssetID
                ,
                MIN(a.PurchDate) ,
                CASE WHEN MAX(CAST(fi.IsQualified AS INT)) = '1' THEN 'Y'
                     ELSE 'N'
                END ,
                MAX(a.ASSQualrat) ,
                MAX(a.ESTDuration) ,
                MAX(i.MoodysRating) ,
                MAX(p.MoodysRating) ,
                MAX(f.MoodysRating) ,
                CASE WHEN SUM(a.Cost) <> '0.0'
                     THEN CAST(CAST(SUM(a.totalIntPurchPrc) AS DECIMAL(38, 12))
                          / CAST(SUM(a.Cost) AS DECIMAL(38, 12)) AS VARCHAR)
                     ELSE 'N/A'
                END ,
                CASE WHEN SUM(a.MktVal) <> '0.0'
                     THEN CAST(CAST(SUM(a.totalintcurrpurchprc) AS DECIMAL(38,
                                                              12))
                          / CAST(SUM(a.MktVal) AS DECIMAL(38, 12)) AS VARCHAR)
                     ELSE '0.0'
                END ,
                CASE WHEN SUM(a.MktVal) <> '0.0'
                     THEN CAST(CAST(SUM(a.totalIntPurchPrc) AS DECIMAL(38, 12))
                          / CAST(SUM(a.MktVal) AS DECIMAL(38, 12)) AS VARCHAR)
                     ELSE 'N/A'
                END ,
                SUM(a.EndAdjAmt) ,
                CASE WHEN SUM(a.AmortCost) <> '0.0'
                     THEN CAST(SUM(a.AmortCost) / SUM(a.EndAdjAmt) AS VARCHAR)
                     ELSE 'N/A'
                END ,
                CASE WHEN SUM(a.EndAdjAmt) <> '0.0'
                     THEN CAST(SUM(a.PurchPrc * a.EndAdjAmt) / SUM(a.EndAdjAmt) AS VARCHAR)
                     ELSE '0.0'
                END ,
                MAX(a.YesterdayPrice) ,
                MAX(a.TodayPrice) ,
                SUM(a.AmortCost) ,
                SUM(a.MktVal) ,
                SUM(a.UnrealDeprec) + SUM(a.unrealapprec) ,
                CASE WHEN SUM(a.AmortCost) <> '0.0'
                     THEN CAST(( SUM(a.UnrealDeprec) + SUM(a.unrealapprec) )
                          / SUM(a.AmortCost) AS VARCHAR)
                     ELSE 'N/A'
                END ,
                SUM(a.DailyValChg) ,
                CASE WHEN MAX(a.YesterdayPrice) > '0.01'
                          AND MAX(a.TodayPrice) > '0.01'
                     THEN CAST(( ( MAX(a.TodayPrice) / MAX(a.YesterdayPrice) )
                                 - 1 ) AS VARCHAR)
                     ELSE 'N/A'
                END ,
                MAX(i.extid) ,
                SUM(a.UnrealDeprec) ,
                SUM(a.UnrealApprec) 
		/*, CASE WHEN MAX(CAST(a.IsProprietary AS INT)) = 1
				THEN 'Y'
				ELSE 'N'
			END as Asset_Proprietary*/ ,
                MAX(ISNULL(a.PropReason, '')) ,
				  MAX(ISNULL(a.PropTrack, '')) ,
			    CASE WHEN MAX(i.PricingMethodType) = 'Cost'
                     THEN 'Cost - to be ' + MAX(i.PricingVendor)
                     ELSE MAX(i.PricingVendor)
                END ,
                SUM(a.totalIntPurchPrc) ,
                SUM(a.totalintcurrpurchprc) ,
                SUM(a.Cost) ,
                MAX(id.InstTypeDescr) ,
                MAX(iss.IssuerName) ,
                COUNT(InstrName) ,
                MAX(iss.SubSector) ,
                MAX(iss.GICSSector) ,
                MAX(i.ExtId) ,
                MAX(p.SortOrder) ,
                MAX(id.sortorder) ,
                MAX(i.MoodysRating) ,
                MAX(i.InstrumentId), 
				MAX(i.MaturityDate),
				SUM(a.SettDiscPurchPrc),
				SUM(a.PIKPurchAmt),
				SUM(a.CoupRevAnnualPurch)

            FROM
                [FSFundModel].[Reporting].Asset a
                INNER JOIN [FSFundModel].[Reporting].Instrument i
                ON a.ExportID = i.ExportID
                   AND a.InstrumentID = i.InstrumentID
                INNER JOIN [FSFundModel].[Reporting].Issuer iss
                ON a.ExportID = iss.ExportID
                   AND i.IssuerID = iss.IssuerID
                INNER JOIN [FSFundModel].[Reporting].InstrumentDefinition id
                ON a.ExportID = id.ExportID
                   AND i.InstDefID = id.InstDefID
                INNER JOIN [FSFundModel].[Reporting].FundIssuer fi
                ON a.ExportID = fi.ExportID
                   AND i.IssuerID = fi.IssuerID
                INNER JOIN [FSFundModel].[Reporting].Portfolio p
                ON a.ExportId = p.ExportId
                   AND A.PortfolioId = p.PortfolioId
                INNER JOIN [FSFundModel].[Reporting].Fund f
                ON a.ExportID = f.ExportID
                   AND F.FundID = @FundID
		            WHERE
                a.ExportID = @ExportID
                AND a.IsActiveEndOfDay = 1
            GROUP BY
                a.PortfolioID ,
                id.InstrClass ,
                i.InstrName
		--, a.AssetID
                ,
                p.SortOrder ,
                id.SortOrder
            --ORDER BY
            --    p.SortOrder ,
            --    id.SortOrder


    INSERT  INTO @results
            ( PortfolioID ,
              PortfolioName ,
              InstrClass ,
              InstrName ,
              OrigPurchDate ,
              IsQualified ,
              AssetQualityRating ,
              EstimateDuration ,
              Moody_Rating ,
              GAAP_Pur ,
              GAAP_Cur ,
              GAAP_Walk ,
              Sum_Par ,
              Amort_Cost_Pct_Par ,
              Weight_Price ,
              PriorNAV_Price ,
              NAVDate_Price ,
              Amort_Cost ,
              MktVal ,
              Net_Unreal_App ,
              Pct_Net_Unreal_Gain ,
              DailyVal_Change ,
              PriceChange_Pct ,
              SecurityID ,
              Sum_Unreal_Dep ,
              Sum_Unreal_App ,
              Prop_Reason ,
              PricingMethod ,
              TotalInt_Purch_Price ,
              TotalInt_Cur_Price ,
              Sum_OrigCost ,
              Security_Type ,
              Issuer_Name ,
              Lots ,
              Iss_SubSector ,
              Iss_Sector ,
              portSortOrder ,
              instSortOrder
			)

            SELECT
                MAX(fci.FundId) ,
                'Unfunded Commitments' ,
                'Unfunded Revolving Credit Facilities and Delayed Draws' ,
                i.InstrName ,
                MIN(a.PurchDate) ,
                fi.IsQualified ,
                MAX(fci.AssQualRat) ,
                NULL ,
                MAX(i.MoodysRating) ,
                NULL ,
                0.0 ,
                NULL ,
                MAX(fci.EndAdjAmt) ,
                ( SUM(fci.AmortCost) / SUM(fci.EndAdjAmt) ) ,
                ( MAX(a.PurchPrc) * MAX(fci.EndAdjAmt) ) / MAX(fci.EndAdjAmt) ,
                NULL ,
                MAX(a.TodayPrice) ,
                SUM(fci.AmortCost) ,
                SUM(fci.MktVal) ,
                SUM(fci.UnrealApprec) ,
                NULL ,
                SUM(fci.DailyValChg) ,
                NULL ,
                MAX(i.ExtId) ,
                SUM(fci.UnrealDeprec) ,
                SUM(fci.UnrealApprec) ,
                NULL ,
                MAX(i.PricingVendor) ,
                NULL ,
                NULL ,
                NULL ,
                MAX(fci.FundCreditInstrumentType) ,
                iss.IssuerName ,
                NULL ,
                MAX(iss.SubSector) ,
                MAX(iss.GICSSector) ,
                '7' ,
                '8'
            FROM
				 [FSFundModel].[Reporting].Asset a           
              
                INNER JOIN [FSFundModel].[Reporting].Instrument i
                ON a.ExportId = i.ExportId
                aND a.InstrumentId = i.InstrumentId --------------------ADDED
            
				INNER JOIN [FSFundModel].[Reporting].Issuer iss
                ON a.ExportId = iss.ExportId
                   AND i.IssuerId = iss.IssuerId
                INNER JOIN [FSFundModel].[Reporting].FundIssuer fi
                ON a.ExportId = fi.ExportId
                   AND i.IssuerId = fi.IssuerId
				   AND iss.IssuerId = fi.IssuerId
                INNER JOIN [FSFundModel].[Reporting].InstrumentDefinition id
                ON a.ExportId = id.ExportId
                   AND i.InstDefId = id.InstDefId

				    INNER JOIN      [FSFundModel].[Reporting].FundCreditInstrument fci
                
                ON a.InstrumentId = fci.InstrumentId
				AND i.InstrumentId = fci.InstrumentId
                   AND a.ExportId = fci.ExportId
            WHERE
                a.ExportId = @ExportID
                AND a.IsActiveEndOfDay = 1
            GROUP BY
                i.InstrName ,
                fi.IsQualified, iss.IssuerName





    DECLARE @totalMktVal DECIMAL(38, 12)
    SET @totalMktVal = ( SELECT
                            SUM(MktVal)
                         FROM
                            @results
                       )
    UPDATE
        r
    SET 
        r.MktVal_Pct = r.MktVal / @totalMktVal
    FROM
        @results r
    UPDATE
        r
    SET 
        r.priorMoodyRating = inst.moodysrating
    FROM
        @results r
        INNER JOIN [FSFundModel].[Reporting].Instrument inst
        ON r.instrumentID = inst.InstrumentId
    WHERE
        inst.ExportId = 1 --Moodys Rating
    UPDATE
        r
    SET 
        --r.PricingDepth = CASE WHEN markit.Depth IS NULL THEN '1'
        --                      WHEN markit.Depth = 'implied' THEN '1'
        --                      ELSE markit.Depth
        --                 END ,
        r.Spread = CASE WHEN p.Offer - p.Bid IS NULL THEN NULL
                        ELSE CAST(p.Offer - p.Bid AS VARCHAR)
                   END ,
        r.Ask = prc2.Offer ,
        r.Bid = prc2.Bid ,
        r.PriorNAV_Price = CASE WHEN r.OrigPurchDate > @LastValcomDate
                                THEN r.Weight_Price
    --                            ELSE 
				----%%REMOVE
    --                                 CASE WHEN r.securityid = '872439AE4'
    --                                      THEN 98.746026 --Wind River
    --                                      WHEN r.securityid = 'ERC_Warrants'
    --                                      THEN 0.0
    --                                      WHEN r.securityid = 'ERC_COMMON'
    --                                      THEN 0.0
    --                                      WHEN r.securityid = 'LX127561'
    --                                      THEN 98.5 -- Affordable Care
    --                                      WHEN r.securityid = 'VRC_CAPITALVISION'
    --                                      THEN 100
    --                                      WHEN r.securityid = 'LX126136'
    --                                      THEN 100.9165  --Generic Drug RC
    --                                      WHEN r.securityid = 'VRC_217'
    --                                      THEN 97.0 -- Leading Edge Revolver
    --                                      WHEN r.securityid = 'LX118857'
    --                                      THEN 95.25

				----END %%REMOVE
                                          ELSE prc.mid
                                     
                           END
    FROM
        @results r
        INNER JOIN [FSFundModel].[Reporting].Instrument i
        ON r.InstrName = i.InstrName
           AND i.ExportID = @ExportID
        LEFT JOIN [FSFundModel].[Reporting].Pricing markit
        ON i.ExtID = markit.PricingID
			AND i.exportid=markit.exportid
           
        LEFT JOIN [FSFundModel].[Reporting].Pricing p
        ON i.ExtID = p.PricingID
           AND i.ExportID = p.ExportID
        LEFT JOIN [FSFundModel].[dbo].Pricing prc
        ON i.extid = prc.PricingId
           AND prc.Pricedate = @LastValcomDate
        LEFT JOIN [FSFundModel].[dbo].Pricing prc2
        ON i.extid = prc2.PricingId
           AND prc2.PriceDate = @NAVDate
    UPDATE
        r
    SET 
        r.pricingDepth = CASE WHEN r.pricingdepth IS NULL THEN '1'
                              ELSE r.pricingDepth
                         END ,
        r.spread = CASE WHEN r.spread IS NULL THEN 'N/A'
                        ELSE CAST(r.spread AS VARCHAR)
                   END
    FROM
        @results r

    IF ( @ReportName = 'FundTotals' ) 
        BEGIN
            SELECT
                f.GAAPYldCurrPrc ,
                f.PurchPrc ,
                f.MoodysRating ,
                f.TodayPrice ,
                f.EndAdjAmt ,
                f.AmortCost ,
                f.MktVal
            FROM
                [FSFundModel].[Reporting].Fund f
            WHERE
                f.ExportId = @ExportID
        END

    IF ( @ReportName = 'Top_20_Valcomm' ) 
        BEGIN
            DECLARE @Top20Issuer TABLE
        (
          IssuerID INT ,
          MktVal DECIMAL(38, 12) ,
          IssuerName VARCHAR(500)
        )
	
            INSERT  INTO @Top20Issuer
                    ( IssuerID ,
                      MktVal ,
                      IssuerName
		            )
                    SELECT TOP 20
                        fi.IssuerID ,
                        fi.MktValLT ,
                        i.IssuerName
                    FROM
                        [FSFundModel].[Reporting].FundIssuer fi
                        INNER JOIN [FSFundModel].[Reporting].Issuer i
                        ON fi.ExportID = i.ExportID
                           AND fi.IssuerID = i.IssuerID
                    WHERE
                        fi.ExportID = @ExportID
                        AND fi.FundID = @FundID
                    ORDER BY
                        fi.MktValLT DESC

	
            SELECT
                r.IssuerName ,
                r.InstrName ,
                r.GAAP_Cur ,
                r.Weight_Price ,
                r.OrigPurchDate ,
                r.PricingDepth ,
                r.Moody_Rating ,
                CASE WHEN r.OrigPurchDate > @LastValcomDate THEN r.Weight_Price
                     ELSE r.PriorNAV_Price
                END AS PriorNAVDate_Price ,
                r.Spread ,
                r.NAVDate_Price AS NAVDate_Price ,
                r.Sum_Par ,
                r.Amort_Cost ,
                i.MktVal AS Issuer_MktVal ,
                r.MktVal AS Instrument_MktVal ,
                CASE WHEN r.OrigPurchDate > @LastValcomDate THEN r.Weight_Price
                     ELSE r.PriorNAV_Price
                END AS PrevQPrice ,
                CASE WHEN r.bid IS NULL THEN 0.0
                     ELSE r.bid
                END AS Bid ,
                CASE WHEN r.ask IS NULL THEN 0.0
                     ELSE r.ask
                END AS ask
			--		then r.prchPrice
			--	else r.prevQuarterPrice
			--end as PrevQPrice  
            FROM
                @results r
                INNER JOIN @Top20Issuer i
                ON r.Issuer_Name = i.IssuerName
            ORDER BY
                i.MktVal DESC ,
                instSortOrder ,
                r.instrname
        END

    IF ( @ReportName LIKE 'ValComm_PortfolioTotal' ) 
        BEGIN

            SELECT
                CASE WHEN p.PortfolioID = 'LOCUS' THEN 'LSF'
                     WHEN p.portfolioid = 'RACES' THEN 'RSF'
                     WHEN p.portfolioid = 'WALN' THEN 'WSF'
                     WHEN p.portfolioid = 'ARCH' THEN 'ASF'
                     WHEN p.portfolioid = 'BROAD' THEN 'BSF'
                     ELSE p.portfolioid
                END AS PortfolioID ,
                p.PortfolioName ,
                p.[GAAPYldCurrPrc] ,
                p.[PurchPrc] ,
                p.[MoodysRating] ,
                p.[TodayPrice] ,
                p.[EndAdjAmt] ,
                p.[AmortCost] ,
                p.[MktVal],
				CASE WHEN p.PortfolioID = 'LOCUS' THEN 101.020883703151
                     WHEN p.portfolioid = 'RACES' THEN 93.6610146513654
                     WHEN p.portfolioid = 'WALN' THEN 100.329108005858
                     WHEN p.portfolioid = 'ARCH' THEN 98.6848984347991
                     WHEN p.portfolioid = 'BROAD' THEN 99.8519847007501
					 WHEN p.PortfolioId = 'FSIC' THEN 92.5488
                     ELSE  98.233712672658
					 END AS PrevPrice
            FROM
                [FSFundModel].[Reporting].Portfolio p
            WHERE
                p.ExportID = @ExportID
                AND p.FundID = @FundID
		
        END

	------------------------------------
	-- Valuation Committee Summary
	------------------------------------
	
    IF ( @ReportName LIKE 'ValComm_Summary%' ) 
        BEGIN
            IF ( @ReportName <> 'ValComm_Summary_Focus' ) 
                BEGIN
		--INSERT INTO @results
		--	(
		--		PortfolioID 
		--		, PortfolioName
		--		, InstrClass 
		--		, InstrName 
		--		, OrigPurchDate
		--		, IsQualified 
		--		, AssetQualityRating 
		--		, EstimateDuration 
		--		, Moody_Rating 
		--		, GAAP_Pur 
		--		, GAAP_Cur 
		--		, GAAP_Walk 
		--		, Sum_Par 
		--		, Amort_Cost_Pct_Par 
		--		, Weight_Price 
		--		, PriorNAV_Price 
		--		, NAVDate_Price 
		--		, Amort_Cost 
		--		, MktVal 
		--		, Net_Unreal_App 
		--		, Pct_Net_Unreal_Gain 
		--		, DailyVal_Change 
		--		, PriceChange_Pct 
		--		, SecurityID 
		--		, Sum_Unreal_Dep 
		--		, Sum_Unreal_App 
		--		, Prop_Reason
		--		, PricingMethod 
		--		, TotalInt_Purch_Price 
		--		, TotalInt_Cur_Price 
		--		, Sum_OrigCost 
		--		, Security_Type
		--		, Issuer_Name
		--		, Lots
		--		, Iss_SubSector
		--		, Iss_Sector
		--		, portSortOrder
		--		, instSortOrder
		--	)
		--select 
		--	fci.FundId
		--	, 'Unfunded Commitments'
		--	, 'Unfunded Revolving Credit Facilities and Delayed Draws'
		--	,i.InstrName
		--	, null
		--	, fi.IsQualified
		--	, fci.AssQualRat
		--	, null
		--	, i.MoodysRating
		--	, null
		--	, 0.0
		--	, null
		--	, fci.EndAdjAmt
		--	, (fci.AmortCost / fci.EndAdjAmt)
		--	, null
		--	, null
		--	, fci.MktVal / fci.EndAdjAmt
		--	, fci.AmortCost
		--	, fci.MktVal
		--	, fci.UnrealApprec
		--	, null
		--	, fci.DailyValChg
		--	, null
		--	, i.ExtId
		--	, fci.UnrealDeprec
		--	, fci.UnrealApprec
		--	, null
		--	, i.PricingVendor
		--	, null
		--	, null
		--	, null
		--	, fci.FundCreditInstrumentType
		--	, iss.IssuerName
		--	, null
		--	, iss.SubSector
		--	, iss.GICSSector
		--	, '8'
		--	, '7'
		--FROM 
		--	[FSFundModel].[Reporting].FundCreditInstrument fci
		--INNER JOIN 
		--	[FSFundModel].[Reporting].Instrument i
		--ON 
		--	fci.ExportId = i.ExportId 
		--AND 
		--	fci.InstrumentId = i.InstrumentId
		--INNER JOIN 
		--	[FSFundModel].[Reporting].Issuer iss
		--ON 
		--	fci.ExportId = iss.ExportId
		--AND 
		--	i.IssuerId = iss.IssuerId
		--INNER JOIN 
		--	[FSFundModel].[Reporting].FundIssuer fi 
		--ON 
		--	fci.ExportId = fi.ExportId
		--AND 
		--	i.IssuerId = fi.IssuerId
		--INNER JOIN 
		--	[FSFundModel].[Reporting].InstrumentDefinition id
		--ON 
		--	fci.ExportId = id.ExportId
		--AND 
		--	i.InstDefId = id.InstDefId
		--WHERE 
		--	fci.ExportId = @ExportID
	
	--UPDATE	 r 
	--	SET r.instrname = r.instrname +'^^'
	--	FROM @results r
		
                    UPDATE
                        r
                    SET 
                        r.instrname = CASE WHEN r.pricingmethod LIKE '%Cost%'
                                           THEN r.instrname + '(6)'
                                           ELSE r.instrname
                                      END
                    FROM
                        @results r
    
                    UPDATE
                        r
                    SET 
                        r.instrname = CASE WHEN r.pricingmethod LIKE '%VRC%'
                                           THEN r.instrname + '(7)'
                                           ELSE r.instrname
                                      END
                    FROM
                        @results r
	
                    UPDATE
                        r
                    SET 
                        r.instrname = CASE WHEN r.Issuer_Name IN (
                                                SELECT DISTINCT
                                                    Issuer_Name
                                                FROM
                                                    @results r
                                                WHERE
                                                    r.pricingmethod = 'VRC' )
                                                AND r.pricingmethod <> 'VRC'
                                           THEN r.instrname + '(8)'
                                           ELSE r.instrname
                                      END
                    FROM
                        @results r

                    UPDATE
                        r
                    SET 
                        r.instrname = CASE WHEN r.AssetQualityRating IN ( 4, 5 )
                                           THEN r.instrname + '(9)'
                                           ELSE r.instrname
                                      END
                    FROM
                        @results r
	            
                END


	
            UPDATE
                r
            SET 
                r.priorMoodyRating = inst.moodysrating
            FROM
                @results r
                INNER JOIN [FSFundModel].[Reporting].Instrument inst
                ON r.instrumentID = inst.InstrumentId
            WHERE
                inst.ExportId = 7

            UPDATE
                r
            SET 
                r.focusreason = CASE WHEN r.pricingmethod LIKE '%Cost%'
                                     THEN 'Asset Marked at Cost'
                                     ELSE ''
                                END
            FROM
                @results r
			       

            UPDATE
                r
            SET 
                r.FocusGroup = CASE WHEN r.AssetQualityRating IN ( '4', '5' )
                                    THEN 'Yes'
                                    WHEN r.PricingMethod LIKE '%VRC%'
                                    THEN 'Yes'
                                    WHEN r.PricingMethod LIKE '%Cost%'
                                    THEN 'Yes'
                                    ELSE 'No'
                               END
            FROM
                @results r
		
            UPDATE
                r
            SET 
                r.FocusReason = CASE WHEN r.FocusReason <> 'Asset marked at Cost'
                                     THEN CASE WHEN r.PricingMethod LIKE '%VRC%'
                                               THEN CASE WHEN r.AssetQualityRating = '4'
                                                         THEN 'Level 4 Asset / VRC'
                                                         WHEN r.AssetQualityRating = '5'
                                                         THEN 'Level 5 Asset / VRC'
                                                         ELSE 'VRC'
                                                    END
                                               ELSE CASE WHEN r.assetQualityRating > 3
                                                         THEN 'Level '
                                                              + CAST(r.assetQualityRating AS VARCHAR)
                                                              + ' Asset'
                                                         ELSE ''
                                                    END
                                          END
                                     ELSE r.focusreason
                                END
            FROM
                @results r
	  --UPDATE
   --     r
   -- SET 
   --     r.FocusReason = CASE WHEN r.FocusReason <> 'Asset marked at Cost'
   --                         then case  WHEN r.PricingMethod LIKE '%VRC%'
   --                                    THEN CASE WHEN r.focusreason <> ''
   --                                              THEN r.focusreason + ' / '
   --                                                   + 'VRC'
   --                                              ELSE 'VRC'
   --                                         END
   --                                    --ELSE r.focusreason
   --        		                end                               
   --                ELSE r.focusreason
			--	   end  
   -- FROM
   --     @results r
    --UPDATE
    --    r
    --SET 
    --    r.focusreason = CASE WHEN  r.FocusReason <> 'Asset marked at Cost' and r.PricingMethod LIKE '%VRC%'
    --                         THEN CASE WHEN r.focusreason = '' THEN 'VRC'
    --                                   ELSE r.focusreason + ' / ' + 'VRC'

    --                              END
    --                         ELSE r.focusreason
    --                    END
    --FROM
    --    @results r                          

            UPDATE
                r
            SET 
                r.FocusGroup = 'Yes' ,
                r.FocusReason = 'Other assets priced by VRC'
            FROM
                @results r
            WHERE
                r.Issuer_Name IN ( SELECT DISTINCT
                                    Issuer_Name
                                   FROM
                                    @results r
                                   WHERE
                                    r.PricingMethod = 'VRC' )
                AND r.FocusGroup = 'No'

	
            SELECT
                CASE WHEN r.PortfolioID = 'LOCUS' THEN 'LSF'
                     WHEN r.portfolioid = 'RACES' THEN 'RSF'
                     WHEN r.portfolioid = 'WALN' THEN 'WSF'
                     WHEN r.portfolioid = 'ARCH' THEN 'ASF'
                     WHEN r.portfolioid = 'BROAD' THEN 'BSF'
                     ELSE r.portfolioid
                END AS PortfolioID ,
                r.PortfolioName ,
                r.InstrClass ,
                r.InstrName ,
                r.OrigPurchDate ,
                r.SecurityID AS Security_Identifier ,
                r.IsQualified AS Is_Qualified ,
                r.AssetQualityRating AS Asset_Quality_Rating ,
                CASE WHEN r.PricingDepth IS NULL THEN '1'
                     ELSE r.pricingdepth
                END AS Depth ,
                r.EstimateDuration AS Estimated_Duration ,
                r.Moody_Rating AS Moody_Rating ,
                CASE WHEN r.Spread IS NULL THEN 'N/A'
                     ELSE CAST(r.Spread AS VARCHAR)
                END AS Spread ,
                r.GAAP_Pur AS GAAP_Yield_At_Purchase_Price ,
                r.GAAP_Cur AS GAAP_Yield_At_Current_Price ,
                r.GAAP_Walk AS Walk_In_GAAP_Yield ,
                r.sum_par AS Sum_Of_Adj_Par ,
            --CASE WHEN InstrClass = 'Equity/Other'
            --          AND ( r.GAAP_Pur = 'N/A' )--r.GAAP_Pur = '0.000000'
            --          THEN '0'
            --     WHEN InstrClass = 'Equity/Other'
            --          AND CAST(r.GAAP_Pur AS DECIMAL(38, 12)) = '0.0' THEN '0'
            --     ELSE CAST(r.Sum_Par AS VARCHAR)
            --END AS Sum_Of_Adj_Par ,
                r.Amort_Cost_Pct_Par AS Amortized_Cost_Pct_of_Par ,
                r.Weight_Price AS Weighted_Avg_Price ,
                r.PriorNAV_Price AS PriorNAVDate_Price  -- fix with dynamic SQL later
                ,
                r.NAVDate_Price AS NAVDate_Price  -- fix with dynamic SQL later
                ,
                r.Amort_Cost AS Sum_Of_Amort_Cost ,
                r.MktVal AS Sum_Of_Mkt_Val ,
                r.MktVal_Pct AS Percent_of_MarketValue ,
                r.Net_Unreal_App AS Net_Unrealized_Appreciation ,
                r.Pct_Net_Unreal_Gain AS Pct_Net_Unreal_Gain ,
                r.DailyVal_Change AS Sum_Of_Daily_Value_Change ,
                r.PriceChange_Pct AS Price_Chg_Pct ,
                r.Sum_Unreal_Dep AS Sum_Of_Unreal_Dep ,
                r.Sum_Unreal_App AS Sum_Of_Unreal_App ,
                r.Prop_Reason AS Proprietary_Reason ,
                r.PricingMethod AS Pricing_Method_Type ,
                r.TotalInt_Purch_Price AS Interest_purchase_price_method ,
                r.TotalInt_Cur_Price AS Interest_current_price_method ,
                r.Sum_OrigCost AS Sum_Of_Original_Cost ,
                r.Security_Type ,
                r.FocusGroup ,
                r.FocusReason ,
                r.portSortOrder ,
                r.instSortOrder ,
                r.Issuer_Name ,
                CASE WHEN r.PriorNAV_Price IS NULL THEN r.PriorNAV_Price
                     WHEN r.OrigPurchDate > @LastValcomDate THEN r.Weight_Price
                     ELSE r.PriorNAV_Price
                END AS prevQuarterPrice ,
                CASE WHEN r.Bid IS NULL THEN 'N/A'
                     ELSE CAST(r.Bid AS VARCHAR)
                END AS Bid ,
                CASE WHEN r.Ask IS NULL THEN 'N/A'
                     ELSE CAST(r.Ask AS VARCHAR)
                END AS Ask ,
                CASE WHEN r.pricingmethod LIKE '%Cost%'
                     THEN 'Investment Memorandum'
                     WHEN r.pricingMethod LIKE '%VRC%' THEN 'Valuation Report'
                     WHEN r.pricingMethod LIKE '%Markit%'
                     THEN 'Markit Screenshot'
                END AS MaterialProvided ,
  dbo.GtMoodysChange(r.moody_rating, r.priormoodyrating) AS moodyChange ,
                r.priorMoodyRating ,
                r.iss_Subsector,
				r.prop_Reason,
				r.Prop_Track,
				r.Maturity_Date,
			r.Settle_Disc_prch,
  r.Pik_Prch ,
  r.Coupon_Purch 
            FROM
                @results r
            WHERE
                r.FocusGroup = CASE WHEN @ReportName = 'ValComm_Summary_Focus'
                                    THEN 'Yes'
                                    ELSE r.FocusGroup
                               END
				--AND r.Prop_Track = CASE WHEN @ReportName like 'ValComm_Summary_Prop%' THEN 'Proprietary'
				--	ELSE r.Prop_Track
				--	END	              
            ORDER BY
                CASE WHEN  @ReportName = 'ValComm_Summary' OR @reportName = 'Valcomm_Summary_Prop' THEN r.portsortorder
				END ASC ,
				--CASE WHEN @ReportName = 'ValComm_Summary_Prop_GSO' THEN r.prop_Reason END ASC,
                r.instsortorder ,
                r.InstrName ASC 
		
        END

END
	
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/Valcom', 'SCHEMA', N'dbo', 'PROCEDURE', N'DailyReporting_ValComm_IC2', NULL, NULL
GO
